import json

import redis
from flask import g, jsonify
from sqlalchemy import create_engine, text

from backend.config import tdengine_connection_params, opcua_adapter_params
from backend.get_logger import logger
from backend.helpers import success_response, error_response
from . import opcua_app


@opcua_app.route('/init_mysql', methods=['POST'])
def init_mysql():
    conn, trans = None, None
    try:
        engine = create_engine('mysql+pymysql://root:root@127.0.0.1:3306/mysql?charset=utf8mb4')
        # 手动控制事务
        conn = engine.connect()
        trans = conn.begin()
        # 执行数据库操作1 创建数据库
        conn.execute(text("""CREATE DATABASE IF NOT EXISTS th 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;"""))
        # 使用数据库
        conn.execute(text("""USE th;"""))
        # 创建数据表
        conn.execute(text("""CREATE TABLE IF NOT EXISTS  `alarm`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `device_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tag_uuid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tag_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `alarm_message` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `alarm_limit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,           -- 创建时间，默认为当前时间
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 更新时间，更新时自动更新为当前时间
  `is_deleted` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 118302 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
"""))
        conn.execute(text("""CREATE TABLE IF NOT EXISTS  `opcuadevices`  (
  `device_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `url` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,           -- 创建时间，默认为当前时间
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 更新时间，更新时自动更新为当前时间
  `is_deleted` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `device_name`(`device_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;"""))
        conn.execute(text("""CREATE TABLE IF NOT EXISTS `opcuapoints`  (
  `tag_uuid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `node_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `interval` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `active` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `active_alarm` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `alarm_up` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `alarm_down` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `alarm_up_info` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `alarm_down_info` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `alarm_up_change` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `alarm_down_change` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `active_archive` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `archive_onchange` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `archive_interval` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `active_scale` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `scale_sign` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `scale_factor` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `mqtt_topic_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `unit` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `comments` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `device_id` int(0) NULL DEFAULT NULL,
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,           -- 创建时间，默认为当前时间
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 更新时间，更新时自动更新为当前时间
  `is_deleted` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `tag_uuid`(`tag_uuid`) USING BTREE,
  INDEX `device_id`(`device_id`) USING BTREE,
  CONSTRAINT `opcuapoints_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `opcuadevices` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;"""))
        trans.commit()  # 提交事务
    except Exception as e:
        trans.rollback()  # 出现异常时回滚事务
        logger.error("事务回滚:", e)
        return error_response(message=str(e))
    finally:
        conn.close()  # 关闭连接
    return success_response(message='ok')


@opcua_app.route('/init_tdengine', methods=['POST'])
def init_tdengine():
    try:
        redis_conn: redis.Redis = g.redis_client
        opcua_info_list = redis_conn.lrange('opcua_info', 0, -1)
        opcua_point_list = []
        for opcua_info in opcua_info_list:
            opcua_point_list.extend(json.loads(opcua_info)['opcua_points'])
        engine = create_engine(
            f"taos://{tdengine_connection_params['username']}:{tdengine_connection_params['password']}@{tdengine_connection_params['host']}:{tdengine_connection_params['port']}/{tdengine_connection_params['database']}")
        # 建立连接
        with engine.connect() as conn:
            # 1. 创建数据库
            try:
                sql1 = f"CREATE DATABASE IF NOT EXISTS {opcua_adapter_params['archive_table_name']} KEEP 3650;"
                conn.execute(text(sql1))
                conn.commit()
            except Exception:
                logger.error(f'{sql1},执行失败')
                conn.rollback()

            # 切穿数据库
            try:
                sql1_1 = f"use {opcua_adapter_params['archive_table_name']};"
                conn.execute(text(sql1_1))
                conn.commit()
            except Exception:
                logger.error(f'{sql1_1},执行失败')
                conn.rollback()

            # 2. 创建超级表
            try:
                sql2 = f"""CREATE STABLE IF NOT EXISTS {opcua_adapter_params['archive_table_name']}.super_table (ts TIMESTAMP, val FLOAT) TAGS (dt BINARY(64), unit BINARY(64), device_id BINARY(16));"""
                conn.execute(text(sql2))
                conn.commit()
            except Exception:
                logger.error(f'{sql2},执行失败')
                conn.rollback()

            # 3. 创建子表
            try:
                for opcua_point in opcua_point_list:
                    sql3 = f"""CREATE TABLE IF NOT EXISTS {opcua_adapter_params['archive_table_name']}.{opcua_point['tag_uuid']} USING super_table TAGS ('{opcua_point["node_id"]}','{opcua_point["unit"]}','{opcua_point["device_id"]}');"""
                    conn.execute(text(sql3))
                    conn.commit()
            except Exception as e:
                logger.error(f'{sql3},执行失败,{e}')
                conn.rollback()
        return success_response(message='ok')
    except Exception as e:
        return error_response(message=str(e))



@opcua_app.route('/test')
def test():
    a = {'1':True, '12345': {'io_read': '34.64 MB',
         'threads': '[[25416, "MainThread", true], '
                    '[25520, "paho-mqtt-client-python_mqtt_client_5", true], '
                    '[25792, "Thread-1 (_run)", true], [22116, "Thread-2", true], '
                    '[29484, "consumer_alarm_info_1", true], [26464, "consumer_alarm_info_2", true], '
                    '[16612, "consumer_alarm_info_3", true], [4900, "consumer_alarm_info_4", true], '
                    '[30312, "consumer_alarm_info_5", true], [23300, "consumer_archive_info_1", true], '
                    '[13784, "consumer_archive_info_2", true], [3540, "consumer_archive_info_3", true], '
                    '[22008, "consumer_archive_info_4", true], [9876, "consumer_archive_info_5", true], '
                    '[19216, "consumer_emqx_info_1", true], [11556, "continuous_thread", true], '
                    '[29840, "monitor_thread", true], [28372, "subscribe_thread", true]]',
         'emqx_consumer_length': '0', 'pid': '26840', 'cpu_usage': '1.6',
         'consumer_archive_info_3_use_time': '1.51', 'total_time': '67',
         'consumer_alarm_info_1_use_time': '0.01', 'archive_consumer_length': '0',
         'disk_usage': '70.6%', 'alarm_count': '22', 'nodes': '100',
         'consumer_alarm_info_5_use_time': '0.01', 'alarm_consumer_length': '100',
         'consumer_archive_info_5_use_time': '1.51', 'consumer_archive_info_1_use_time': '1.50',
         'exception_time': '0', 'process_name': 'python.exe', 'continuous_thread_use_time': '0.03',
         'consumer_alarm_info_4_use_time': '0.00', 'consumer_archive_info_4_use_time': '1.50',
         'consumer_alarm_info_2_use_time': '0.01', 'consumer_archive_info_2_use_time': '1.50',
         'memory_info_RSS': '161.38 MB', 'io_write': '0.01 MB', 'consumer_alarm_info_3_use_time': '0.00',
         'memory_info_VMS': ' 623.76 MB', 'buffer': '100'}}


    return success_response(data=a)

